/**
 * 
 */
package xx.teach.entity;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import javax.sql.RowSet;

import xx.db.QueryRunner;
import xx.db.annotation.Column;
import xx.db.annotation.TableName;
import xx.db.sql.Owner;
import xx.db.sql.Table;

/**
 * @author Administrator 2014-3-18
 */
@TableName(name = "t_schedule")
public class Schedule extends Table
{

    /**
     * @param qr
     * @param owner
     */
    public Schedule(QueryRunner qr, Owner owner)
    {
        super(qr, owner);
        // TODO Auto-generated constructor stub
    }

    public static final int NOT_START_STATUS = 0;// 未开始打卡

    public static final int START_STATUS = 1;// 开始打卡

    public static final int FINISH_STATUS = 2;// 完成

    // public static final int LATE_STATUS = 3;// 迟到
    //
    // public static final int LEAVE_STATUS = 4;// 请假
    //
    // public static final int LEAVE_EARLY_STATUS = 5;// 早退

    public static final String SCHEDULE_ID = "schedule_id";

    @Column(name = SCHEDULE_ID, pk = true)
    private long scheduleId;

    public static final String ACCOUNT_ID = "account_id";

    @Column(name = ACCOUNT_ID)
    private long accountId;

    public static final String GROUP_ID = "group_id";

    @Column(name = GROUP_ID)
    private long groupId;

    public static final String CLASS_TIME = "class_time";

    @Column(name = CLASS_TIME)
    private Timestamp classTime;

    public static final String STATUS = "status";

    @Column
    private int status;

    public static final String SEQUENCE = "sequence";

    @Column
    private int sequence;

    public static final String ARRIVE_TIME = "arrive_time";

    @Column(name = ARRIVE_TIME)
    private Timestamp arriveTime;

    public static final String LEAVE_TIME = "leave_time";

    @Column(name = LEAVE_TIME)
    private Timestamp leaveTime;

    public long getScheduleId()
    {
        return scheduleId;
    }

    public Schedule setScheduleId(long scheduleId)
    {
        this.scheduleId = scheduleId;
        return this;
    }

    public long getAccountId()
    {
        return accountId;
    }

    public Schedule setAccountId(long accountId)
    {
        this.accountId = accountId;
        return this;
    }

    public long getGroupId()
    {
        return groupId;
    }

    public Schedule setGroupId(long groupId)
    {
        this.groupId = groupId;
        return this;
    }

    public Timestamp getClassTime()
    {
        return classTime;
    }

    public Schedule setClassTime(Timestamp classTime)
    {
        this.classTime = classTime;
        return this;
    }

    public int getStatus()
    {
        return status;
    }

    public Schedule setStatus(int status)
    {
        this.status = status;
        return this;
    }

    public int getSequence()
    {
        return sequence;
    }

    public Schedule setSequence(int sequence)
    {
        this.sequence = sequence;
        return this;
    }

    public Timestamp getArriveTime()
    {
        return arriveTime;
    }

    public Schedule setArriveTime(Timestamp arriveTime)
    {
        this.arriveTime = arriveTime;
        return this;
    }

    public Timestamp getLeaveTime()
    {
        return leaveTime;
    }

    public Schedule setLeaveTime(Timestamp leaveTime)
    {
        this.leaveTime = leaveTime;
        return this;
    }

    /**
     * 老师只能在上课前后30分钟这段时间内开启考勤
     * 
     * @return
     * @throws SQLException
     */
    public int updateStatusOnClass() throws SQLException
    {
        if (groupId == 0)
            return 0;
        Timestamp beforeTime = new Timestamp(System.currentTimeMillis());
        beforeTime.setHours(beforeTime.getMinutes() - 30);

        Timestamp befindTime = new Timestamp(System.currentTimeMillis());
        befindTime.setHours(befindTime.getMinutes() + 30);
        String cnd = Schedule.GROUP_ID + "=" + groupId + " and " + Schedule.CLASS_TIME + " between '" + beforeTime + "' and '" + befindTime + "' and " + Schedule.STATUS + "="
                + Schedule.NOT_START_STATUS;
        return update.update(Schedule.class).set(Schedule.STATUS, START_STATUS).where(cnd).go();
    }

    /**
     * 老师在下课后关闭考勤
     * 
     * @return
     * @throws SQLException
     */
    public int updateStatusDownClass() throws SQLException
    {
        if (groupId == 0)
            return 0;
        String cnd = Schedule.GROUP_ID + "=" + groupId + " and " + Schedule.STATUS + "=" + Schedule.START_STATUS;
        return update.update(Schedule.class).set(Schedule.STATUS, FINISH_STATUS).where(cnd).go();
    }

    /**
     * 学生只能在老师开启考勤功能后，参与考勤
     * 
     * @return
     * @throws SQLException
     */
    public int updateArriveTimeByStudent() throws SQLException
    {
        if (accountId == 0)
            return 0;

        Timestamp arriveTime = new Timestamp(System.currentTimeMillis());
        String cnd = Schedule.ACCOUNT_ID + "=" + accountId + " and " + Schedule.STATUS + "=" + Schedule.START_STATUS;
        return update.update(Schedule.class).set(Schedule.ARRIVE_TIME, arriveTime).where(cnd).go();
    }

    /**
     * 学生只能在老师开启考勤功能后，参与考勤
     * 
     * @return
     * @throws SQLException
     */
    public int updateLeaveTimeByStudent() throws SQLException
    {
        if (accountId == 0)
            return 0;

        Timestamp leaveTime = new Timestamp(System.currentTimeMillis());
        String cnd = Schedule.ACCOUNT_ID + "=" + accountId + " and " + Schedule.STATUS + "=" + Schedule.START_STATUS;
        return update.update(Schedule.class).set(Schedule.LEAVE_TIME, leaveTime).where(cnd).go();
    }

    /**
     * 统计正常签到的名单(上课时间10分钟后到达的算迟到)
     * 
     * @return
     * @throws SQLException
     */
    public RowSet calculate() throws SQLException
    {
        if (groupId == 0)
            return null;

        String cnd = Schedule.GROUP_ID + "=" + groupId + " and UNIX_TIMESTAMP(" + Schedule.ARRIVE_TIME + ")-UNIX_TIMESTAMP(" + Schedule.CLASS_TIME + ") between 0 and 60*10 and "
                + Schedule.STATUS + "=1";
        return select.from(Account.class, Account.NAME).leftJoin(Schedule.class).using(Schedule.ACCOUNT_ID).where(cnd).query();

    }

    /**
     * 统计迟到名单(上课时间10分钟后到达的算迟到)
     * 
     * @return
     * @throws SQLException
     */
    public RowSet calculateLate() throws SQLException
    {
        if (groupId == 0)
            return null;

        String cnd = Schedule.GROUP_ID + "=" + groupId + " and UNIX_TIMESTAMP(" + Schedule.ARRIVE_TIME + ")-UNIX_TIMESTAMP(" + Schedule.CLASS_TIME + ") > 60*10 and "
                + Schedule.STATUS + "=1";
        return select.from(Account.class, Account.NAME).leftJoin(Schedule.class).using(Schedule.ACCOUNT_ID).where(cnd).query();

    }

    /**
     * 获取sequence
     * 
     * @return
     * @throws SQLException
     */
    private int getNextSequence() throws SQLException
    {
        if (groupId == 0)
            throw new SQLException("获取最大序列时，groupId 不能为零");

        RowSet rs = select.from(Schedule.class, "max(" + Schedule.SEQUENCE + ")").where(Schedule.GROUP_ID + "=" + groupId).query();
        rs.next();
        return rs.getInt(1);
    }

    /*
     * (non-Javadoc)
     * @see xx.db.sql.Table#insert()
     */
    @Override
    public long insert() throws SQLException
    {
        this.setSequence(getNextSequence() + 1);
        return super.insert();
    }

    /**
     * 根据groupId，为班级中的每个学生插入时间表
     * @return
     * @throws SQLException
     */
    public int insertByGroupId() throws SQLException
    {
        if (groupId == 0)
            throw new SQLException("groupId 不能为零");

        int count = 0;
        List<Account> accountList = select.from(Account.class, Account.ACCOUNT_ID).where(Account.GROUP_ID + "=" + groupId).go();
        for (Account account : accountList)
        {
            Schedule schedule = new Schedule(qr, owner);
            schedule.setGroupId(groupId).setAccountId(account.getAccountId()).setClassTime(classTime).setStatus(NOT_START_STATUS).insert();
            count++;
        }
        return count;
    }
}
